{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import lightgbm as lgb\n",
    "from sklearn.metrics import roc_auc_score\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "from sklearn.model_selection import StratifiedKFold"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_path = '../../../contest/train/'\n",
    "stage_path = '../../../contest/A榜/'\n",
    "stage = 'A'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = pd.read_csv('../../../contest/train/DZ_TARGET_TRAIN.csv')\n",
    "df_test = pd.read_csv('../../../contest/A榜/DZ_TARGET_TESTA.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train_user = pd.DataFrame({'CUST_NO':df_train.CUST_NO})\n",
    "df_test_user = pd.DataFrame({'CUST_NO':df_test.cust_no})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 跨行转账表（DZ_TR_IBTF）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp_train = pd.read_csv(os.path.join(train_path,'DZ_TR_IBTF.csv'))\n",
    "tmp_test = pd.read_csv(os.path.join(stage_path,f'DZ_TR_IBTF_{stage}.csv'))\n",
    "\n",
    "tmp_train = tmp_train.fillna(0)\n",
    "tmp_test = tmp_test.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['DATA_DAT', 'CUST_NO', 'IBTF_MOTH_TR_AMT', 'IBTF_YEAR_TR_AMT',\n",
       "       'IBTF_MOTH_NET_TR_AMT', 'IBTF_YEAR_NET_TR_AMT', 'IBTF_MOTH_TR_AMT_IN',\n",
       "       'IBTF_YEAR_TR_AMT_IN', 'IBTF_MOTH_TR_CNT', 'IBTF_YEAR_TR_CNT',\n",
       "       'IBTF_MOTH_TR_CNT_IN', 'IBTF_YEAR_TR_CNT_IN'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp_train.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fe_asset(df):\n",
    "    df = df.drop(['DATA_DAT'],axis=1)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp_train = fe_asset(tmp_train)\n",
    "tmp_test = fe_asset(tmp_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp_train = df_train_user.merge(tmp_train,on='CUST_NO',how='left')\n",
    "tmp_test = df_test_user.merge(tmp_test,on='CUST_NO',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CUST_NO</th>\n",
       "      <th>IBTF_MOTH_TR_AMT</th>\n",
       "      <th>IBTF_YEAR_TR_AMT</th>\n",
       "      <th>IBTF_MOTH_NET_TR_AMT</th>\n",
       "      <th>IBTF_YEAR_NET_TR_AMT</th>\n",
       "      <th>IBTF_MOTH_TR_AMT_IN</th>\n",
       "      <th>IBTF_YEAR_TR_AMT_IN</th>\n",
       "      <th>IBTF_MOTH_TR_CNT</th>\n",
       "      <th>IBTF_YEAR_TR_CNT</th>\n",
       "      <th>IBTF_MOTH_TR_CNT_IN</th>\n",
       "      <th>IBTF_YEAR_TR_CNT_IN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>235e4e193124d8c55095cf3f0f0d8f35</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>f1b5ca32a8f7ef5430f5775c00ff3f60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>51be6f380b408edeb7779b76e016dcd3</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>50.338974</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>18.245871</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>40.578453</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ccd7e33ccbe7e9dd4246a2959f666c0a</td>\n",
       "      <td>36.491741</td>\n",
       "      <td>105.077335</td>\n",
       "      <td>36.491741</td>\n",
       "      <td>18.245871</td>\n",
       "      <td>36.491741</td>\n",
       "      <td>83.545232</td>\n",
       "      <td>5.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>069f48f51bf6be5bcbdc9af52bb20970</td>\n",
       "      <td>26.271167</td>\n",
       "      <td>83.960901</td>\n",
       "      <td>26.271167</td>\n",
       "      <td>83.960901</td>\n",
       "      <td>26.271167</td>\n",
       "      <td>83.960901</td>\n",
       "      <td>5.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            CUST_NO  IBTF_MOTH_TR_AMT  IBTF_YEAR_TR_AMT  \\\n",
       "0  235e4e193124d8c55095cf3f0f0d8f35          0.000000          0.000000   \n",
       "1  f1b5ca32a8f7ef5430f5775c00ff3f60               NaN               NaN   \n",
       "2  51be6f380b408edeb7779b76e016dcd3          0.000000         50.338974   \n",
       "3  ccd7e33ccbe7e9dd4246a2959f666c0a         36.491741        105.077335   \n",
       "4  069f48f51bf6be5bcbdc9af52bb20970         26.271167         83.960901   \n",
       "\n",
       "   IBTF_MOTH_NET_TR_AMT  IBTF_YEAR_NET_TR_AMT  IBTF_MOTH_TR_AMT_IN  \\\n",
       "0              0.000000              0.000000             0.000000   \n",
       "1                   NaN                   NaN                  NaN   \n",
       "2              0.000000             18.245871             0.000000   \n",
       "3             36.491741             18.245871            36.491741   \n",
       "4             26.271167             83.960901            26.271167   \n",
       "\n",
       "   IBTF_YEAR_TR_AMT_IN  IBTF_MOTH_TR_CNT  IBTF_YEAR_TR_CNT  \\\n",
       "0             0.000000               3.0               3.0   \n",
       "1                  NaN               NaN               NaN   \n",
       "2            40.578453               3.0               7.0   \n",
       "3            83.545232               5.0              33.0   \n",
       "4            83.960901               5.0              29.0   \n",
       "\n",
       "   IBTF_MOTH_TR_CNT_IN  IBTF_YEAR_TR_CNT_IN  \n",
       "0                  3.0                  3.0  \n",
       "1                  NaN                  NaN  \n",
       "2                  3.0                  5.0  \n",
       "3                  5.0                 31.0  \n",
       "4                  5.0                 29.0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#pd.set_option('display.max_columns',None)\n",
    "tmp_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp_train.to_csv('../fea/train_tr_ibtf.csv',index=False)\n",
    "tmp_test.to_csv('../fea/test_tr_ibtf.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
